* clean barcelona elections data (2015 and 2019)
********************************************************************************
* prelims
********************************************************************************
{ 
clear all
cap log close
set more off

}
********************************************************************************
* raw to dta
********************************************************************************
{
foreach etype in 02 {

	local type congreso
	local eyears 201911 201512
	
	* loop over election years
	foreach yearmo in `eyears' {

		* identify the specific year
		local year = substr("`yearmo'",1,4)
		local yymm = substr("`yearmo'",3,4)

	* 1.- Fichero de CONTROL de los ficheros que componen el proceso electoral.
	****************************************************************************
* import file
import delimited "$orig/elections/`etype'`yearmo'_MESA/01`etype'`yymm'.DAT", varnames(nonames) encoding(ISO-8859-1) stringcols(_all) clear

* generate vars
g ElecType = substr(v1,1,2)
g ElecYear = substr(v1,3,4)
g ElecMonth = substr(v1,7,2)
g f1 = substr(v1,10,1)
g f2 = substr(v1,11,1)
g f3 = substr(v1,12,1)
g f4 = substr(v1,13,1)
g f5 = substr(v1,14,1)
g f6 = substr(v1,15,1)
g f7 = substr(v1,16,1)
g f8 = substr(v1,17,1)
g f9 = substr(v1,18,1)
g f10 = substr(v1,19,1)
g f11 = substr(v1,20,1)
g f12 = substr(v1,21,1)
g f0510 = substr(v1,22,1)
g f0610 = substr(v1,23,1)
g f0710 = substr(v1,24,1)
g f0810 = substr(v1,25,1)

drop v1

* label
la var ElecType "Tipo de elección"
la var ElecYear "Año del proceso electoral"
la var ElecMonth "Mes del proceso electoral"
la var f1 "Siempre 1 (se adjunta fichero 01xxaamm.dat)"
la var f2 "1/0 (se adjunta/no se adjunta el fichero 02xxaamm.dat)"
la var f3 "1/0 (se adjunta/no se adjunta el fichero 03xxaamm.dat)"
la var f4 "1/0 (se adjunta/no se adjunta el fichero 04xxaamm.dat)"
la var f5 "1/0 (se adjunta/no se adjunta el fichero 05xxaamm.dat)"
la var f6 "1/0 (se adjunta/no se adjunta el fichero 06xxaamm.dat)"
la var f7 "1/0 (se adjunta/no se adjunta el fichero 07xxaamm.dat)"
la var f8 "1/0 (se adjunta/no se adjunta el fichero 08xxaamm.dat)"
la var f9 "1/0 (se adjunta/no se adjunta el fichero 09xxaamm.dat)"
la var f10 "1/0 (se adjunta/no se adjunta el fichero 10xxaamm.dat)"
la var f11 "1/0 (se adjunta/no se adjunta el fichero 1104aamm.dat)"
la var f12 "1/0 (se adjunta/no se adjunta el fichero 1204aamm.dat)"
la var f0510 "1/0 (se adjunta/no se adjunta el fichero 0510aamm.dat)"
la var f0610 "1/0 (se adjunta/no se adjunta el fichero 0610aamm.dat)"
la var f0710 "1/0 (se adjunta/no se adjunta el fichero 0710aamm.dat)"
la var f0810 "1/0 (se adjunta/no se adjunta el fichero 0810aamm.dat)"

* store
compress
save "$data/temp/`type'`yearmo'_f1.dta", replace

	* 2.- Fichero de IDENTIFICACION del proceso electoral
	****************************************************************************
* import file
import delimited "$orig/elections/`etype'`yearmo'_MESA/02`etype'`yymm'.DAT", varnames(nonames) encoding(ISO-8859-1) stringcols(_all) clear

* generate vars
g ElecType = substr(v1,1,2)
g Ambito = substr(v1,10,1)
g ElecDay = substr(v1,13,2)
g ElecMonth = substr(v1,15,2)
g ElecYear = substr(v1,17,4)
g ElecHrStart = substr(v1,21,5)
g ElecHrEnd = substr(v1,26,5)
g HrAvance1 = substr(v1,31,5)
g HrAvance2 = substr(v1,36,5)

* label
la var ElecType "Tipo de elección"
la var Ambito "Tipo de ámbito (N=nacional, A=autonómico)"
la var ElecDay "Fecha de celebración del proceso electoral (DIA)"
la var ElecMonth "Fecha de celebración del proceso electoral (MES)"
la var ElecYear "Fecha de celebración del proceso electoral (AÑO)"
la var ElecHrStart "Hora de apertura de los ‘Colegios Electorales’"
la var ElecHrEnd "Hora de cierre de los ‘Colegios Electorales’"
la var HrAvance1 "Hora del primer ‘Avance de Participación’"
la var HrAvance2 "Hora del segundo ‘Avance de Participación"

drop v1

* store
compress
save "$data/temp/`type'`yearmo'_f2.dta", replace

	* 3.- Fichero de CANDIDATURAS
	****************************************************************************
import delimited "$orig/elections/`etype'`yearmo'_MESA/03`etype'`yymm'.DAT", varnames(nonames) encoding(ISO-8859-1) stringcols(_all) clear

* replacing special characters
	* upper case
replace v1 = subinstr(v1,"Ñ","N",.)
replace v1 = subinstr(v1,"Ç","C",.)
replace v1 = subinstr(v1,"Á","A",.)
replace v1 = subinstr(v1,"À","A",.)
replace v1 = subinstr(v1,"Ä","A",.)
replace v1 = subinstr(v1,"È","E",.)
replace v1 = subinstr(v1,"É","E",.)
replace v1 = subinstr(v1,"Í","I",.)
replace v1 = subinstr(v1,"Ó","O",.)
replace v1 = subinstr(v1,"Ò","O",.)
replace v1 = subinstr(v1,"Ö","O",.)
replace v1 = subinstr(v1,"Ú","U",.)
replace v1 = subinstr(v1,"Ù","U",.)
replace v1 = subinstr(v1,"Ü","U",.)
	* lower case
replace v1 = subinstr(v1,"ñ","n",.)
replace v1 = subinstr(v1,"ç","c",.)
replace v1 = subinstr(v1,"á","a",.)
replace v1 = subinstr(v1,"à","a",.)
replace v1 = subinstr(v1,"ä","a",.)
replace v1 = subinstr(v1,"è","e",.)
replace v1 = subinstr(v1,"é","e",.)
replace v1 = subinstr(v1,"í","i",.)
replace v1 = subinstr(v1,"ï","i",.)
replace v1 = subinstr(v1,"ó","o",.)
replace v1 = subinstr(v1,"ò","o",.)
replace v1 = subinstr(v1,"ö","o",.)
replace v1 = subinstr(v1,"ú","u",.)
replace v1 = subinstr(v1,"ù","u",.)
replace v1 = subinstr(v1,"ü","u",.)
	* other special characters
replace v1 = subinstr(v1,"ª"," ",.)
replace v1 = subinstr(v1,"«","(",.)
replace v1 = subinstr(v1,"»",")",.)

* generate vars
g ElecType = substr(v1,1,2)
g ElecYear = substr(v1,3,4)
g ElecMonth = substr(v1,7,2)
g PartyCode = substr(v1,9,6)
g PartyAcr = substr(v1,15,50)
g PartyName = substr(v1,65,150)
g PartyCodeProv = substr(v1,215,6)
g PartyCodeReg = substr(v1,221,6)
g PartyCodeNat = substr(v1,227,6)

* label
la var ElecType "Tipo de elección"
la var ElecMonth "Fecha de celebración del proceso electoral (MES)"
la var ElecYear "Fecha de celebración del proceso electoral (AÑO)"
la var PartyCode "Código de la candidatura"
la var PartyAcr "Siglas de la candidatura"
la var PartyName "Denominación de la candidatura"
la var PartyCodeProv "Código de la candidatura cabecera de acumulación a nivel provincial"
la var PartyCodeReg "Código de la candidatura cabecera de acumulación a nivel autonómico"
la var PartyCodeNat "Código de la candidatura cabecera de acumulación a nivel nacional"

drop v1

* clean strings
replace PartyName = ustrregexra(PartyName,"[\n\r\t]"," ")
replace PartyAcr = ustrregexra(PartyAcr,"[\n\r\t]"," ")
replace PartyName = strtrim(PartyName)
replace PartyAcr = strtrim(PartyAcr)

* store
compress
save "$data/temp/`type'`yearmo'_f3.dta", replace

	* 4.- Fichero de RELACION DE CANDIDATOS
	****************************************************************************
	
	* 1999 cabildos
	if ("`etype'" == "06" & `year' == 1999 ) == 0 {
	
* load
import delimited "$orig/elections/`etype'`yearmo'_MESA/04`etype'`yymm'.DAT", varnames(nonames) encoding(ISO-8859-1) stringcols(_all) clear 

* replacing special characters
	* upper case
replace v1 = subinstr(v1,"Ñ","N",.)
replace v1 = subinstr(v1,"Ç","C",.)
replace v1 = subinstr(v1,"Á","A",.)
replace v1 = subinstr(v1,"À","A",.)
replace v1 = subinstr(v1,"Ä","A",.)
replace v1 = subinstr(v1,"È","E",.)
replace v1 = subinstr(v1,"É","E",.)
replace v1 = subinstr(v1,"Í","I",.)
replace v1 = subinstr(v1,"Ó","O",.)
replace v1 = subinstr(v1,"Ò","O",.)
replace v1 = subinstr(v1,"Ö","O",.)
replace v1 = subinstr(v1,"Ú","U",.)
replace v1 = subinstr(v1,"Ù","U",.)
replace v1 = subinstr(v1,"Ü","U",.)
	* lower case
replace v1 = subinstr(v1,"ñ","n",.)
replace v1 = subinstr(v1,"ç","c",.)
replace v1 = subinstr(v1,"á","a",.)
replace v1 = subinstr(v1,"à","a",.)
replace v1 = subinstr(v1,"ä","a",.)
replace v1 = subinstr(v1,"è","e",.)
replace v1 = subinstr(v1,"é","e",.)
replace v1 = subinstr(v1,"í","i",.)
replace v1 = subinstr(v1,"ï","i",.)
replace v1 = subinstr(v1,"ó","o",.)
replace v1 = subinstr(v1,"ò","o",.)
replace v1 = subinstr(v1,"ö","o",.)
replace v1 = subinstr(v1,"ú","u",.)
replace v1 = subinstr(v1,"ù","u",.)
replace v1 = subinstr(v1,"ü","u",.)
	* other special characters
replace v1 = subinstr(v1,"ª"," ",.)
replace v1 = subinstr(v1,"«","(",.)
replace v1 = subinstr(v1,"»",")",.)

* generate vars
g ElecType = substr(v1,1,2)
g ElecYear = substr(v1,3,4)
g ElecMonth = substr(v1,7,2)
g ProvCode = substr(v1,10,2)
g DistrictCode = substr(v1,12,1)
g MunCode = substr(v1,13,3)
g PartyCode = substr(v1,16,6)
g CandOrder = substr(v1,22,3)
g CandType = substr(v1,25,1)
g CandName = substr(v1,26,25)
g CandSurname1 = substr(v1,51,25)
g CandSurname2 = substr(v1,76,25)
g CandGender = substr(v1,101,1)
g CanBirthDay = substr(v1,102,2)
g CandBirthMonth = substr(v1,104,2)
g CandBirthYear = substr(v1,106,4)
g CandDNI = substr(v1,110,10)
g CandElected = substr(v1,120,1)

drop v1

* label
la var ElecType "Tipo de elección"
la var ElecMonth "Fecha de celebración del proceso electoral (MES)"
la var ElecYear "Fecha de celebración del proceso electoral (AÑO)"
la var ProvCode "Código I.N.E.de la provincia (99 en elecciones al Parlamento Europeo)"
la var DistrictCode "Distrito electoral cuando corresponda o 9 en elecciones que no tienen este tipo de circunscripción"
la var MunCode "Código I.N.E. del municipio "
la var PartyCode "Código de la candidatura"
la var CandOrder "Número de orden del candidato" 
la var CandType "Tipo de candidato (T = Titular, S = Suplente)"
la var CandName "Nombre del candidato"
la var CandSurname1 "Primer apellido del candidato" 
la var CandSurname2 "Segundo apellido del candidato"
la var CandGender "Sexo del candidato (Masculino/Femenino)"
la var CandBirthMonth "Fecha de nacimiento del candidato (DIA)"
la var CanBirthDay "Fecha de nacimiento del candidato (MES)"
la var CandBirthYear "Fecha de nacimiento del candidato (AÑO)"
la var CandDNI "D.N.I. del candidato"
la var CandElected "Candidato elegido (Si/No)"

* clean strings
foreach var in CandName CandSurname1 CandSurname2 {

	replace `var' = ustrregexra(`var',"[\n\r\t]"," ")
	replace `var' = strtrim(`var')

}

* store
compress
save "$data/temp/`type'`yearmo'_f4.dta", replace
	} /*account for cabildos 1999*/

	* 5.- Fichero de DATOS COMUNES DE MUNICIPIOS
	****************************************************************************
* load data
import delimited "$orig/elections/`etype'`yearmo'_MESA/05`etype'`yymm'.DAT", varnames(nonames) encoding(ISO-8859-1) clear stringcols(_all)

* replacing special characters
	* upper case
replace v1 = subinstr(v1,"Ñ","N",.)
replace v1 = subinstr(v1,"Ç","C",.)
replace v1 = subinstr(v1,"Á","A",.)
replace v1 = subinstr(v1,"À","A",.)
replace v1 = subinstr(v1,"Ä","A",.)
replace v1 = subinstr(v1,"È","E",.)
replace v1 = subinstr(v1,"É","E",.)
replace v1 = subinstr(v1,"Í","I",.)
replace v1 = subinstr(v1,"Ó","O",.)
replace v1 = subinstr(v1,"Ò","O",.)
replace v1 = subinstr(v1,"Ö","O",.)
replace v1 = subinstr(v1,"Ú","U",.)
replace v1 = subinstr(v1,"Ù","U",.)
replace v1 = subinstr(v1,"Ü","U",.)
	* lower case
replace v1 = subinstr(v1,"ñ","n",.)
replace v1 = subinstr(v1,"ç","c",.)
replace v1 = subinstr(v1,"á","a",.)
replace v1 = subinstr(v1,"à","a",.)
replace v1 = subinstr(v1,"ä","a",.)
replace v1 = subinstr(v1,"è","e",.)
replace v1 = subinstr(v1,"é","e",.)
replace v1 = subinstr(v1,"í","i",.)
replace v1 = subinstr(v1,"ï","i",.)
replace v1 = subinstr(v1,"ó","o",.)
replace v1 = subinstr(v1,"ò","o",.)
replace v1 = subinstr(v1,"ö","o",.)
replace v1 = subinstr(v1,"ú","u",.)
replace v1 = subinstr(v1,"ù","u",.)
replace v1 = subinstr(v1,"ü","u",.)
	* other special characters
replace v1 = subinstr(v1,"ª"," ",.)
replace v1 = subinstr(v1,"«","(",.)
replace v1 = subinstr(v1,"»",")",.)

* generate vars
g ElecType = substr(v1,1,2)
g ElecYear = substr(v1,3,4)
g ElecMonth = substr(v1,7,2)
g RegCode = substr(v1,10,2)
g ProvCode = substr(v1,12,2)
g MunCode = substr(v1,14,3)
g DistrictCode = substr(v1,17,2)
g MunName = substr(v1,19,100)
g ElecDistrictCode = substr(v1,119,1)
g PartyJudCode = substr(v1,120,3)
g ProvDipCode = substr(v1,123,3)
g ComarcaCode = substr(v1,126,3)
g Pop = substr(v1,129,8)
g Mesas = substr(v1,137,5)
g CensusINE = substr(v1,142,8)
g CensusEscr = substr(v1,150,8)
g CensusCERE = substr(v1,158,8)
g VotesCERE = substr(v1,166,8)
g VotesAvance1 = substr(v1,174,8)
g VotesAvance2 = substr(v1,182,8)
g VotesBlank = substr(v1,190,8)
g VotesNull = substr(v1,198,8)
g VotesParties = substr(v1,206,8)
g Seats = substr(v1,214,3)

* drop unnecessary variables
drop v1

* label
la var ElecType "Tipo de elección"
la var ElecMonth "Fecha de celebración del proceso electoral (MES)"
la var ElecYear "Fecha de celebración del proceso electoral (AÑO)"
la var RegCode "Código de la Comunidad Autónoma"
la var ProvCode "Código I.N.E. de la provincia"
la var MunCode "Código I.N.E. del municipio"
la var DistrictCode "Número de distrito municipal en su caso o 99 si es el total municipal"
la var MunName "Nombre del municipio o del distrito municipal"
la var ElecDistrictCode "Código del Distrito Electoral cuando corresponda o 0 en elecciones que no tienen este tipo de circunscripción"
la var PartyJudCode "Código del Partido Judicial"
la var ProvDipCode "Código de la Diputación Provincial"
la var ComarcaCode "Código de la comarca"
la var Pop "Población de derecho"
la var Mesas "Número de mesas"
la var CensusINE "Censo del I.N.E" 
la var CensusEscr "Censo de escrutinio"
la var CensusCERE "Censo C.E.R.E. en escrutinio (Residentes Extranjeros)"
la var VotesCERE "Total votantes C.E.R.E. (Residentes Extranjeros)"
la var VotesAvance1 "Votantes del primer avance de participación"
la var VotesAvance2 "Votantes del segundo avance de participación"
la var VotesBlank "Votos en blanco"
la var VotesNull "Votos nulos"
la var VotesParties "Votos a candidaturas" 
la var Seats "Número de ‘Escaños’ a distribuir cuando el municipio es la circunscripción electoral. Ceros en otros casos"

* get rid of white spaces in the end for the names
foreach var in MunName {

	* replace all newline, carriage return, and tab characters with a space
	replace `var' = ustrregexra(`var',"[\n\r\t]"," ")
	* blanks in the end
	replace `var' = strtrim(`var')

}

* store
compress
save "$data/temp/`type'`yearmo'_f5.dta", replace

	* 6.- Fichero de DATOS DE CANDIDATURAS DE MUNICIPIOS.
	****************************************************************************
* load data
import delimited "$orig/elections/`etype'`yearmo'_MESA/06`etype'`yymm'.DAT", varnames(nonames) encoding(ISO-8859-1) clear stringcols(_all)

* generate vars
g ElecType = substr(v1,1,2)
g ElecYear = substr(v1,3,4)
g ElecMonth = substr(v1,7,2)
g ProvCode = substr(v1,10,2)
g MunCode = substr(v1,12,3)
g DistrictCode = substr(v1,15,2)
g PartyCode = substr(v1,17,6)
g VotesParty = substr(v1,23,8)
g SeatsParty = substr(v1,31,3)

drop v1

* label
la var ElecType "Tipo de elección"
la var ElecMonth "Fecha de celebración del proceso electoral (MES)"
la var ElecYear "Fecha de celebración del proceso electoral (AÑO)"
la var ProvCode "Código I.N.E. de la provincia"
la var MunCode "Código I.N.E. del municipio"
la var DistrictCode "Número de distrito municipal en su caso o 99 si es el total municipal"
la var PartyCode "Código de la candidatura o del Senador"
la var VotesParty "Votos obtenidos por la candidatura"
la var SeatsParty "Número de candidatos obtenidos por la candidatura"

* store
compress
save "$data/temp/`type'`yearmo'_f6.dta", replace


	* 7.- Fichero de DATOS COMUNES DE AMBITO SUPERIOR AL MUNICIPIO
	****************************************************************************
* load data
import delimited "$orig/elections/`etype'`yearmo'_MESA/07`etype'`yymm'.DAT", varnames(nonames) encoding(ISO-8859-1) clear stringcols(_all)

* replacing special characters
	* upper case
replace v1 = subinstr(v1,"Ñ","N",.)
replace v1 = subinstr(v1,"Ç","C",.)
replace v1 = subinstr(v1,"Á","A",.)
replace v1 = subinstr(v1,"À","A",.)
replace v1 = subinstr(v1,"Ä","A",.)
replace v1 = subinstr(v1,"È","E",.)
replace v1 = subinstr(v1,"É","E",.)
replace v1 = subinstr(v1,"Í","I",.)
replace v1 = subinstr(v1,"Ó","O",.)
replace v1 = subinstr(v1,"Ò","O",.)
replace v1 = subinstr(v1,"Ö","O",.)
replace v1 = subinstr(v1,"Ú","U",.)
replace v1 = subinstr(v1,"Ù","U",.)
replace v1 = subinstr(v1,"Ü","U",.)
	* lower case
replace v1 = subinstr(v1,"ñ","n",.)
replace v1 = subinstr(v1,"ç","c",.)
replace v1 = subinstr(v1,"á","a",.)
replace v1 = subinstr(v1,"à","a",.)
replace v1 = subinstr(v1,"ä","a",.)
replace v1 = subinstr(v1,"è","e",.)
replace v1 = subinstr(v1,"é","e",.)
replace v1 = subinstr(v1,"í","i",.)
replace v1 = subinstr(v1,"ï","i",.)
replace v1 = subinstr(v1,"ó","o",.)
replace v1 = subinstr(v1,"ò","o",.)
replace v1 = subinstr(v1,"ö","o",.)
replace v1 = subinstr(v1,"ú","u",.)
replace v1 = subinstr(v1,"ù","u",.)
replace v1 = subinstr(v1,"ü","u",.)
	* other special characters
replace v1 = subinstr(v1,"ª"," ",.)
replace v1 = subinstr(v1,"«","(",.)
replace v1 = subinstr(v1,"»",")",.)

* generate vars
g ElecType = substr(v1,1,2)
g ElecYear = substr(v1,3,4)
g ElecMonth = substr(v1,7,2)
g RegCode = substr(v1,10,2)
g ProvCode = substr(v1,12,2)
g DistrictCode = substr(v1,14,1)
g ProvName = substr(v1,15,50)
g Pop = substr(v1,65,8)
g Mesas = substr(v1,73,5)
g CensusINE = substr(v1,78,8)
g CensusEscr = substr(v1,86,8)
g CensusCERE = substr(v1,94,8)
g VotesCERE = substr(v1,102,8)
g VotesAvance1 = substr(v1,110,8)
g VotesAvance2 = substr(v1,118,8)
g VotesBlank = substr(v1,126,8)
g VotesNull = substr(v1,134,8)
g VotesParties = substr(v1,142,8)
g Seats = substr(v1,150,6)
g OfficialData = substr(v1,172,1)

drop v1

* label
la var ElecType "Tipo de elección"
la var ElecMonth "Fecha de celebración del proceso electoral (MES)"
la var ElecYear "Fecha de celebración del proceso electoral (AÑO)"
la var RegCode "Código de la Comunidad Autónoma"
la var ProvCode "Código I.N.E. de la provincia"
la var DistrictCode "Número de distrito municipal en su caso o 99 si es el total municipal"
la var ProvName "Nombre del ámbito territorial"
la var Pop "Población de derecho"
la var Mesas "Número de mesas"
la var CensusINE "Censo del I.N.E" 
la var CensusEscr "Censo de escrutinio"
la var CensusCERE "Censo C.E.R.E. en escrutinio (Residentes Extranjeros)"
la var VotesCERE "Total votantes C.E.R.E. (Residentes Extranjeros)"
la var VotesAvance1 "Votantes del primer avance de participación"
la var VotesAvance2 "Votantes del segundo avance de participación"
la var VotesBlank "Votos en blanco"
la var VotesNull "Votos nulos"
la var VotesParties "Votos a candidaturas" 
la var Seats "Número de ‘Escaños’ a distribuir cuando el ámbito coincida con la circunscripción electoral o Total de ‘Escaños’ distribuidos en el ámbito. Ceros en el caso de que el ámbito sea inferior a la circunscripción electoral"
la var OfficialData "Datos oficiales (Si/No)"

* store
compress
save "$data/temp/`type'`yearmo'_f7.dta", replace

	* 8.- Fichero de DATOS DE CANDIDATURAS DE AMBITO SUPERIOR AL MUNICIPIO
	****************************************************************************
* load data
import delimited "$orig/elections/`etype'`yearmo'_MESA/08`etype'`yymm'.DAT", varnames(nonames) encoding(ISO-8859-1) clear stringcols(_all)

* generate vars
g ElecType = substr(v1,1,2)
g ElecYear = substr(v1,3,4)
g ElecMonth = substr(v1,7,2)
g RegCode = substr(v1,10,2)
g ProvCode = substr(v1,12,2)
g DistrictCode = substr(v1,14,1)
g PartyCode = substr(v1,15,6)
g VotesParty = substr(v1,21,8)
g SeatsParty = substr(v1,29,5)

drop v1

* label
la var ElecType "Tipo de elección"
la var ElecMonth "Fecha de celebración del proceso electoral (MES)"
la var ElecYear "Fecha de celebración del proceso electoral (AÑO)"
la var RegCode "Código de la Comunidad Autónoma"
la var ProvCode "Código I.N.E. de la provincia"
la var DistrictCode "Número de distrito municipal en su caso o 99 si es el total municipal"
la var PartyCode "Código de la candidatura o del Senador"
la var VotesParty "Votos obtenidos por la candidatura"
la var SeatsParty "Número de candidatos obtenidos por la candidatura"

* store
compress
save "$data/temp/`type'`yearmo'_f8.dta", replace

	* 9.- Fichero de DATOS COMUNES DE MESAS y del C.E.R.A.
	****************************************************************************
* load data
import delimited "$orig/elections/`etype'`yearmo'_MESA/09`etype'`yymm'.DAT", varnames(nonames) encoding(ISO-8859-1) clear stringcols(_all)

* generate vars
g ElecType = substr(v1,1,2)
g ElecYear = substr(v1,3,4)
g ElecMonth = substr(v1,7,2)
g RegCode = substr(v1,10,2)
g ProvCode = substr(v1,12,2)
g MunCode = substr(v1,14,3)
g DistrictCode = substr(v1,17,2)
g SectionCode = substr(v1,19,4)
g MesaCode = substr(v1,23,1)
g CensusINE = substr(v1,24,7)
g CensusEscr = substr(v1,31,7)
g CensusCERE = substr(v1,28,7)
g VotesCERE = substr(v1,45,7)
g VotesAvance1 = substr(v1,52,7)
g VotesAvance2 = substr(v1,59,7)
g VotesBlank = substr(v1,66,7)
g VotesNull = substr(v1,73,7)
g VotesParties = substr(v1,80,7)
g OfficialData = substr(v1,101,1)

drop v1

* label
la var ElecType "Tipo de elección"
la var ElecMonth "Fecha de celebración del proceso electoral (MES)"
la var ElecYear "Fecha de celebración del proceso electoral (AÑO)"
la var RegCode "Código de la Comunidad Autónoma"
la var ProvCode "Código I.N.E. de la provincia"
la var MunCode "Código I.N.E. del municipio (999 = C.E.R.A.)"
la var DistrictCode "Número de distrito municipal en su caso o 99 si es el total municipal"
la var SectionCode "Código de la sección (tres dígitos seguidos de un espacio, letra mayúscula u otro dígito)"
la var MesaCode "Código de la mesa (una letra mayúscula identificando la mesa o una ‘U’ en caso de mesa única)"
la var CensusINE "Censo del I.N.E" 
la var CensusEscr "Censo de escrutinio o censo C.E.R.A."
la var CensusCERE "Censo C.E.R.E. en escrutinio (Residentes Extranjeros)"
la var VotesCERE "Total votantes C.E.R.E. (Residentes Extranjeros)"
la var VotesAvance1 "Votantes del primer avance de participación"
la var VotesAvance2 "Votantes del segundo avance de participación"
la var VotesBlank "Votos en blanco"
la var VotesNull "Votos nulos"
la var VotesParties "Votos a candidaturas" 
la var OfficialData "Datos oficiales (Si/No)"

* store
compress
save "$data/temp/`type'`yearmo'_f9.dta", replace

	* 10.- Fichero de DATOS DE CANDIDATURAS DE MESAS y del C.E.R.A.
	****************************************************************************
* load data
import delimited "$orig/elections/`etype'`yearmo'_MESA/10`etype'`yymm'.DAT", varnames(nonames) encoding(ISO-8859-1) clear stringcols(_all)

* generate vars
g ElecType = substr(v1,1,2)
g ElecYear = substr(v1,3,4)
g ElecMonth = substr(v1,7,2)
g RegCode = substr(v1,10,2)
g ProvCode = substr(v1,12,2)
g MunCode = substr(v1,14,3)
g DistrictCode = substr(v1,17,2)
g SectionCode = substr(v1,19,4)
g MesaCode = substr(v1,23,1)
g PartyCode = substr(v1,24,6)
g VotesParty = substr(v1,30,7)

drop v1

* label
la var ElecType "Tipo de elección"
la var ElecMonth "Fecha de celebración del proceso electoral (MES)"
la var ElecYear "Fecha de celebración del proceso electoral (AÑO)"
la var RegCode "Código de la Comunidad Autónoma"
la var ProvCode "Código I.N.E. de la provincia"
la var MunCode "Código I.N.E. del municipio (999 = C.E.R.A.)"
la var DistrictCode "Número de distrito municipal en su caso o 99 si es el total municipal"
la var SectionCode "Código de la sección (tres dígitos seguidos de un espacio, letra mayúscula u otro dígito)"
la var MesaCode "Código de la mesa (una letra mayúscula identificando la mesa o una ‘U’ en caso de mesa única)"
la var PartyCode "Código de la candidatura o del Senador"
la var VotesParty "Votos obtenidos por la candidatura"

* store
compress
save "$data/temp/`type'`yearmo'_f10.dta", replace

	} 
} 
}
********************************************************************************
* generate party rank in each election
********************************************************************************
{

foreach etype in 02 {

	local type congreso
	local eyears 201911 201512

	* loop over election years
	foreach yearmo in `eyears' {

		* identify the specific year
		local year = substr("`yearmo'",1,4)
		local yymm = substr("`yearmo'",3,4)
	

* load electoral data
use "$data/temp/`type'`yearmo'_f10.dta", clear
	
* destring numerical variable
destring Votes*, force replace
* aggregate by partycode
bys PartyCode: egen VotesPartyElec = sum(VotesParty)
la var VotesPartyElec "Total Votes Party in the Election"
* keep one observation per party
duplicates drop PartyCode, force
* keep variables necessary to identify electoral process
keep ElecType ElecYear ElecMonth PartyCode VotesPartyElec

** add party information
* merge with party info table
merge 1:1 PartyCode using "$data/temp/`type'`yearmo'_f3.dta"
* it is a problem if there are parties for which I cannot find the name
replace VotesPartyElec=0 if _merge==2
drop _merge
* aggregate votes at national level
bys PartyCodeNat: egen VotesNatPartyElec = sum(VotesPartyElec)
la var VotesNatPartyElec "Total Votes Party (National) in the Election"
* put bigger parties first
gsort -VotesNatPartyElec
* generate rank in election
g x=0
replace x = x[_n] + 1 if PartyCodeNat != PartyCodeNat[_n-1] & _n>1
g rank = 1
replace rank = rank[_n-1] + x if _n>1
drop x
la var rank "Party Rank in the Election (National)"

** store
compress
order Elec* PartyCode* Party* Votes*
save "$data/temp/`type'`yearmo'_PartyRank.dta", replace

	} 
} 

* combine data
clear
foreach etype in 02 { 

	local type congreso
	local eyears 201911 201512
	
	foreach yearmo in `eyears' {

		local year = substr("`yearmo'",1,4)
		local yymm = substr("`yearmo'",3,4)
	
	append using "$data/temp/`type'`yearmo'_PartyRank.dta"
	cap erase "$data/temp/`type'`yearmo'_PartyRank.dta"
	
	} 
}

cap drop v2 v3

* store
gsort ElecType -ElecYear -VotesNatPartyElec
compress
save "$data/temp/AllElec_PartyRank.dta", replace
}
********************************************************************************
* party allocation
********************************************************************************
{
* parties
use "$data/temp/AllElec_PartyRank.dta", clear 

gsort ElecType -ElecYear -VotesNatPartyElec -VotesPartyElec

* party identifier
egen partytag = tag(ElecType ElecYear ElecMonth PartyCodeNat)

* main national parties
keep if partytag == 1
drop PartyCode PartyCodeProv PartyCodeReg
drop partytag

* upper case
g v1 = upper(PartyAcr)
g v2 = upper(PartyName)

* replace special characters
replace v1 = subinstr(v1,".","",.) 
replace v2 = subinstr(v2,".","",.) 

* irrelevant parties
drop if rank>20

* top parties
	* PP
g pp = (regexm(v2,"PARTIDO POPULAR")==1|regexm(v1,"^PP$")==1|regexm(v1,"^AP$")==1|regexm(v1,"^AP[\-]")==1)
la var pp "PP - PARTIDO POPULAR"

	* PSOE
g psoe = (regexm(v1,"PSOE")==1|regexm(v1,"PSC")==1|regexm(v2,"PARTIDO SOCIALISTA DE EUSKADI")==1)
la var psoe "PSOE - PARTIDO SOCIALISTA OBRERO ESPAÑOL"

	* IU
#d;
g iu = (regexm(v1,"^I[\.]*U[\.]*")==1
	|regexm(v1,"IU[\-]+")==1
	|regexm(v1,"[\-]+IU")==1
	|regexm(v1,"^ADELANTE$")==1
	|regexm(v2,"IZQUIERDA UNIDA")==1
	|regexm(v2,"ESQUERRA UNIDA PAIS VALENCIA")==1
	|regexm(v2,"IZQUIERDA UNIDA[\-]MADRID EN PIE MUNICIPALISTA")==1)
	;
#d cr
la var iu "IU - IZQUIERDA UNIDA"

	* PODEMOS
#d;
g podemos = (regexm(v2,"PODEMOS")==1|regexm(v2,"EN COMU")==1|regexm(v1,"CATSIQUEESPOT")==1
	|regexm(v1,"EN MAREA")==1|regexm(v2,"EN MAREA")==1|regexm(v2,"AHORA MADRID")==1
	|regexm(v1,"^PODEMOS$")==1
	|regexm(v1,"^PODEMOS[\-]+")==1
	|regexm(v1,"^IU PODEMOS$")==1
	|regexm(v1,"^IU[\-]PODEMOS$")==1
	|regexm(v1,"^PODEMOS IU$")==1
	|regexm(v1,"^PODEMOS[\-]IU")==1
	|regexm(v1,"^UNIDAS PODEMOS")==1
	|regexm(v2,"UNIDAS PODEMOS$")==1
	|regexm(v2,"^UNIDAS PODEMOS")==1
	|regexm(v2,"^UNIDES PODEM")==1
	|regexm(v2,"ELKARREKIN PODEMOS")==1
	|regexm(v2,"UNIDAS IZQUIERDA UNIDA")==1
	|regexm(v2,"IZQUIERDA UNIDA PODEMOS")==1
	|regexm(v1,"^UNIDES PODEM")==1
	|regexm(v1,"^UNIDES[\-]*PODEM")==1
	|regexm(v1,"^UNIDAS[\-]*IU")==1)
	;
#d cr
la var podemos "PODEMOS"

	* MAS PAIS - NOV 2019
#d;
	g maspais = (regexm(v2,"^MAS PAIS$")==1
	|regexm(v1,"^MAS MADRID")==1
	|regexm(v2,"^MAS MADRID")==1);
#d cr
la var maspais "MAS PAIS"

	* CIUDADANOS
#d;
g cs = ((regexm(v1,"^C[\']*[\´]*S")==1
	|regexm(v1,"C[\']*[\´]*S[\-]+")==1
	|regexm(v1,"[\-]+C[\']*[\´]*S")==1)
	& regexm(v2,"COALICION SOCIALDEMOCRATA")==0)
	;
#d cr
la var cs "C's - CIUDADANOS"

	* UPyD
g upyd = (regexm(v1,"U[\.]*P[\.]*Y[\.]*D[\.]*")==1)
la var upyd "UPyD - UNION PROGRESO Y DEMOCRACIA"	

	* CiU
#d;
g ciu = (regexm(v1,"^CIU$")==1|regexm(v2,"CONVERGENCIA I UNIO")==1
	|regexm(v1,"[\-]CIU$")==1|regexm(v1,"CIU[\-]")==1)
	;
#d cr	
la var ciu "CiU - CONVERGENCIA I UNIO"

	* ERC
g erc = (regexm(v1,"ERC")==1|regexm(v2,"ESQUERRA REPUBLICANA")==1)
la var erc "ERC - ESQUERRA REPUBLICANA DE CATALUNYA"

	* JxSi
g jxsi = (regexm(v1,"JXS[I]*[\Í]*")==1|regexm(v2,"JUNTS PEL SI")==1)
la var jxsi "JxSi - JUNTS PEL SI"	

	* ICV
#d;
g icv = (regexm(v1,"I[\.]*C[\.]*V[\.]*")==1
	|regexm(v2,"INICIATIVA PER CATALUNYA")==1
	|regexm(v2,"INIC[\.] PER CATALUNYA")==1
	|regexm(v2,"ESQUERRA UNIDA I ALTERNATIVA")==1)
	;
#d cr
la var icv "ICV - INICIATIVA PER CATALUNYA VERDS"

	* CUP
#d;
g cup = (regexm(v1,"C[\.]*U[\.]*P[\.]*")==1
	|regexm(v2,"CANDIDATURA [D]*[\']* UNITAT POPULAR")==1
	|(regexm(v1,"CUP")==1 & regexm(v1,"AMUNT")==1)
	|(regexm(v2,"CUP")==1 & regexm(v2,"ALTERNATIVA MUNICIPALISTA")==1)
	|(regexm(v1,"AMUNT")==1 & regexm(v2,"ALTERNATIVA MUNICIPALISTA")==1))
	;
#d cr
la var cup "CUP - CANDIDATURA D'UNITAT POPULAR"

	* PRIMARIES
#d;
	g primaries = (regexm(v2,"BARCELONA ES CAPITAL")==1
	|regexm(v1,"^PRIMARIES$")==1)
	;
#d cr
la var primaries "PRIMARIES"

	* PNV
g pnv = (regexm(v1,"PNV")==1|regexm(v2,"PARTIDO NACIONALISTA VASCO")==1)
la var pnv "PNV - PARTIDO NACIONALISTA VASCO"

	* UPN
g upn = regexm(v1,"UPN")==1
la var upn "UPN - UNION DEL PUEBLO NAVARRO"

	* BNG
g bng = (regexm(v1,"BNG")==1|regexm(v2,"BLOQUE NACIONALISTA GAL")==1)
la var bng "BNG - BLOQUE NACIONALISTA GALEGO"

	* BILDU
g bildu = (regexm(v1,"BILDU")==1|regexm(v2,"BILDU")==1)
la var bildu "EH BILDU"

	* PACMA
g pacma = (regexm(v1,"PACMA")==1|regexm(v2,"PACMA")==1)
la var pacma "PACMA"

	* VOX
g vox = (regexm(v1,"VOX")==1)
la var vox "VOX"

	* JxCAT - Catalunya
#d;
	g jxcat = (regexm(v1,"JXCAT")==1|regexm(v1,"JUNTSXCAT")==1
	|regexm(v2,"JUNTS PER CATALUNYA")==1|regexm(v1,"^JUNTS$")==1
	|(regexm(v1,"JUNTS")==1 & regexm(v1,"JXCAT")==1)
	|regexm(v1,"^JXCATS$")==1)
	;
#d cr
la var jxcat "JxCat - JUTNS PER CATALUNYA"

	* COMPROMIS - Valencia
#d;
	g compromis = (regexm(v1,"^COMPROMIS")==1
	|regexm(v1,"^BLOC$")==1
	|regexm(v1,"^BLOC[\-]")==1
	|regexm(v2,"UPV[\-]BLOC NACIONALISTA")==1
	|regexm(v2,"^MES COMPROMIS")==1
	|regexm(v2,"COMPROMIS PER EUROPA$")==1)
	;
#d cr
la var compromis "COMPROMIS"

	* FRONT REPUBLICA - Catalunya Congreso 2019
g fr = (regexm(v1,"FRONT REPUB")==1)
la var fr "FR - FRONT REPUBLICA"

	* COALICION CANARIA
g cc = (regexm(v1,"CCA[\-]*PNC")==1|regexm(v2,"COALICION CANARIA")==1)
la var cc "CC - COALICIÓN CANARIA"

	* CDC - Catalunya
#d;
g cdc = (regexm(v2,"CONVERGENCIA DEMOCRATICA DE CATALUNYA")==1
	|regexm(v2,"DEMOCRACIA I LLIBERTAT")==1);
#d cr
la var cdc "CDC - CONVERGENCIA DEMOCRATICA DE CATALUNYA"

	* UDC - Catalunya
g udc = (regexm(v2,"UNIO DEMOCRATICA DE CATALUNYA")==1)
la var udc "UDC - UNIO DEMOCRATICA DE CATALUNYA"

	* SI
g si = (regexm(v2,"SOLIDARITAT CATALANA PER LA INDEPENDENCIA")==1)
la var si "SI - SOLIDARITAT CATALANA PER LA INDEPENDENCIA"

	* REAGRUPAMENT
g ricat = (regexm(v2,"REAGRUPAMENT INDEPENDENTISTA")==1)
la var ricat "RICAT - REAGRUPAMENT INDEPENDENTISTA"

	* PA 
#d;
g pa = (regexm(v2,"PARTIDO ANDALUCISTA")==1|regexm(v2,"COALICION ANDALUCISTA")==1
		|regexm(v1,"[\-]PA$")==1);
#d cr
la var pa "PA - PARTIDO ANDALUCISTA"

	* CHA
g cha = (regexm(v2,"CHUNTA ARAGONESISTA")==1)
la var cha "CHA - CHUNTA ARAGONESISTA"

	* UV
g uv = (regexm(v2,"UNIO VALENCIANA")==1)
la var uv "UV - UNION VALENCIANA"

	* PAR
g par = (regexm(v2,"PARTIDO ARAGONES")==1)
la var par "PAR - PARTIDO ARAGONES"

	* CCD
#d;
	g ccd = (regexm(v2,"CIUDADANOS DE CENTRO DEMOCRATICO")==1
	|regexm(v2,"CENTRO DEMOCRATICO Y SOCIAL")==1
	|regexm(v2,"UNION CENTRISTA")==1
	|regexm(v2,"CENTRE DEMOCRATIC I SOCIAL")==1
	|regexm(v2,"COALICION FORO Y CDS")==1)
	;
#d cr
la var ccd "CCD - CIUDADANOS DE CENTRO DEMOCRATICO"

	* FAC
#d;
	g fac = (regexm(v2,"FORO DE ASTURIAS")==1
	|regexm(v1,"^FAC$")==1
	|regexm(v2,"FORO DE CIUDADANOS")==1)
	;
#d cr
la var fac "FAC - FORO DE ASTURIAS"

	* LIBERTAS
g libertas = (regexm(v2,"^LIBERTAS")==1)
la var libertas "LIBERTAS"

	* EQUO
g equo = (regexm(v1,"EQUO")==1|regexm(v2,"EQUO")==1)
la var equo "EQUO"

	* Oher Green
#d;
	g green = ((regexm(v2,"LOS VERDES")==1
	|regexm(v2,"VERDE[S]*")==1
	|regexm(v1,"VERDE[S]*")==1
	|regexm(v2,"VERDS")==1
	|regexm(v2,"^LOS ECOLOGISTAS$")==1
	|regexm(v1,"^RECORTES CE")==1)
	& (iu==0 & icv==0) & (equo==0))
	;
#d cr
la var green "Other green party"

	* Communists
#d;
	g communists = (regexm(v2,"INIZIATIBA INTERNAZIONALISTA")==1
	|regexm(v1,"PCPE")==1
	|regexm(v2,"PARTIDO COMUNISTA DE LOS TRABAJADORES DE ESPANA")==1
	|regexm(v2,"PARTIDO COMUNISTA DE ESPANA")==1
	|regexm(v1,"^PASOC")==1
	|regexm(v2,"IZQUIERDA ANTICAPITALISTA")==1
	|regexm(v1,"^POSI")==1
	|regexm(v2,"PARTIDO OBRERO SOCIALISTA INTERNACIONALISTA")==1
	|regexm(v2,"UNIDAD COMUNISTA")==1
	|regexm(v2,"PARTIDO COMUNISTA OBRERO ESPANOL")==1
	|regexm(v2,"PARTIT COMUNISTA DEL POBLE DE CATALUNYA")==1
	|regexm(v2,"COMUNISTAS")==1
	|regexm(v2,"PARTIDO REVOLUCIONARIO DE LOS TRABAJADORES")==1
	|regexm(v2,"PARTIDO SOCIALISTA DE LOS TRABAJADORES$")==1
	|regexm(v2,"PARTIT DELS COMUNISTES DE CATALUNYA$")==1
	|regexm(v2,"UNIFICACIO COMUNISTA D'ESPANYA")==1
	|regexm(v2,"PARTIDO COMUNISTA DEL PUEBLO CANARIO")==1
	|regexm(v2,"LLUITA INTERNACIONALISTA")==1)
	;
#d cr
la var communists "Communist parties"

	* Other Canary Islands Parties
#d;
	g canary = ((regexm(v2,"UNIDOS POR GRAN CANARIA")==1
	|regexm(v2,"AGRUPACION SOCIALISTA GOMERA")==1
	|regexm(v2,"ALTERNATIVA SI SE PUEDE POR TENERIFE")==1
	|regexm(v2,"COMPROMISO POR GRAN CANARIA")==1
	|regexm(v2,"COALICION ELECTORAL CENTRO CANARIO")==1
	|regexm(v2,"PARTIDO DE INDEPENDIENTES DE LANZAROTE")==1
	|regexm(v2,"UNION CANARIA")==1
	|regexm(v2,"PARTIDO NACIONALISTA CANARIO")==1
	|regexm(v2,"ALTERNATIVA POPULAR CANARIA")==1
	|regexm(v2,"ALTERNATIVA CIUDADANA 25 DE MAYO")==1
	|regexm(v1,"^NCA$")==1|regexm(v1,"^NC[\-]*FA$")==1
	|regexm(v2,"NUEVA CANARIAS")==1
	|regexm(v2,"ALTERNATIVA NACIONALISTA CANARIA")==1
	|regexm(v2,"INDEPENDIENTES DE FUERTEVENTURA")==1
	|regexm(v2,"CENTRO CANARIO NACIONALISTA")==1
	|regexm(v2,"SENTIDO COMUN EN CANARIAS")==1
	|regexm(v2,"PARTIDO PROGRESISTA MAJORERO")==1
	|regexm(v2,"CENTRO CANARIO REFORMISTA")==1
	|regexm(v2,"INICIATIVA POR LA PALMA")==1
	|regexm(v2,"COALICION DE ASAMBLEA CONEJERA")==1
	|regexm(v2,"PARTIDO DE GRAN CANARIA")==1
	|regexm(v2,"AGRUPACION HERRENA INDEPENDIENTE")==1
	|regexm(v2,"AGRUPACION TINERFENA DE INDEPENDIENTES")==1
	|regexm(v2,"CENTRO CANARIO")==1
	|regexm(v2,"CIUDADANOS PARA EL CAMBIO CANARIO")==1
	|regexm(v2,"AGRUPACIONES INDEPENDIENTES DE CANARIAS")==1
	|regexm(v2,"SOMOS LANZAROTE")==1)
	& cc==0)
	;
#d cr
la var canary "Other Canary Islands Parties"

	* Other Andalucia Parties
#d;
	g andalucia = ((regexm(v2,"^PARTIDO SOCIALISTA DE ANDALUCIA$")==1
	|regexm(v2,"ANDALUCIA POR SI")==1
	|regexm(v2,"PARTIDO ANDALUZ DE PROGRESO")==1)
	& pa == 0)
	;
#d cr
la var andalucia "Other Andalucian Parties"

	* NOS
g nos = (regexm(v2,"NOS")==1 & regexm(v2,"CANDIDATURA GALEGA")==1)
la var nos "NOS - CANDIDATURA GALEGA"

	* Other Galician
#d;
	g galicia = ((regexm(v2,"TERRA GALEGA")==1 
	|regexm(v2,"COALICION GALEGA")==1)
	& bng==0)
	;
#d cr
la var galicia "Other Andalucian Parties"

	* NAFARROA BAI
g nabai = (regexm(v1,"^NABAI$")==1|regexm(v2,"NAFARROA BAI")==1)
la var nabai "NABAI - NAFARROA BAI"

	* GEROA BAI
g geroabai = (regexm(v2,"GEROA BAI")==1|regexm(v1,"^GBAI$")==1)
la var geroabai "GEROA BAI - GEROA BAI"

	* AMAIUR
g amaiur = (regexm(v2,"AMAIUR")==1)
la var amaiur "AMAIUR"
	
	* ARALAR
g aralar = (regexm(v2,"ARALAR")==1)
la var aralar "ARALAR"

	* BATASUNA
g batasuna = (regexm(v2,"BATASUNA")==1)
la var batasuna "BATASUNA"

	* EA - EUSKO ALKARTASUNA
g ea = (regexm(v2,"EUSKO ALKARTASUNA")==1)
la var ea "EA - EUSKO ALKARTASUNA"

	* PSM-ENTESA
#d;
g psmen = (regexm(v2,"PSM ENTESA NACIONALISTA")==1
	|regexm(v2,"COALICION ELECTORAL PSM[\-]ENE")==1
	|regexm(v2,"PSM[\-]*[ ]*ENTESA NACIONALISTA")==1)
	;
#d cr
la var psmen "PSM-ENTESA - ENTESA NACIONALISTA"

	* Other Basques
#d;
	g basque = ((regexm(v2,"EUSKO ABERTZALE")==1
	|regexm(v2,"EUSKAL HERRITARROK")==1
	|regexm(v2,"EUSKADIKO EZQUERRA")==1
	|regexm(v2,"EUSKADIKO EZKERRA")==1)
	& (bildu==0 & pnv==0) & (nabai==0) & (batasuna==0) & ea==0)
	;
#d cr
la var basque "Other Basque Parties"

	* PRC
#d;
	g prc = (regexm(v1,"^PRC$")==1
	|regexm(v2,"PARTIDO REGIONALISTA [DE ]*CANTABRIA")==1
	|regexm(v2,"^PARTIDO REGIONALISTA DE CANTABRIA$")==1)
	;
#d cr
la var prc "PRC - PARTIDO REGIONALISTA CANTABRIA"

	* Other Regionalist
#d;
	g regionalist = ((regexm(v2,"UNION DEL PUEBLO LEONES")==1
	|regexm(v2,"UNION RENOVADORA ASTURIANA")==1
	|regexm(v2,"PARTIU ASTURIANISTA")==1
	|regexm(v2,"NAVARRA SUMA")==1)
	& (cha == 0 & par==0))
	;
#d cr
la var regionalist "Other Regionalists Parties"

	* Other Parties
#d;
	g other = (regexm(v2,"PARTIDO CANNABIS")==1
	|regexm(v2,"POR UN MUNDO MAS JUSTO")==1
	|regexm(v2,"EN BLANCO$")==1
	|regexm(v2,"ESCONS EN BLANC")==1
	|regexm(v2,"ESCONS INSUBMISOS")==1
	|regexm(v2,"PIRAT[A]*[ES]*")==1
	|regexm(v2,"PARTIDO HUMANISTA")==1
	|regexm(v2,"^PARTIDO X")==1
	|(regexm(v2,"GRUPO INDEPENDIENTE LIBERAL")==1 & regexm(v1,"GIL")==1)
	|regexm(v2,"MOVIMIENTO RED")==1
	|regexm(v2,"FAMILIA [Y]*[I]* VIDA")==1
	|regexm(v2,"CONFEDERACION DE ORGANIZACIONES FEMINISTAS")==1
	|regexm(v2,"AGRUPACION RUIZ-MATEOS")==1
	|regexm(v1,"RUIZ-MATEOS")==1
	|regexm(v2,"PARTI[T]*[DO]* HUMANISTA")==1)
	;
#d cr
la var other "Other Parties"

	* Other Extreme Right
#d;
	g extright = (regexm(v2,"ALTERNATIVA ESPANOLA")==1
	|regexm(v2,"ESPANA 2000")==1
	|regexm(v2,"FALANGE")==1
	|regexm(v2,"PLATAFORMA PER CATALUNYA")==1
	|regexm(v2,"DEMOCRACIA NACIONAL")==1
	|(regexm(v2,"FE")==1 & regexm(v2,"JONS")==1))
	;
#d cr
la var extright "Other Extreme Right"

* unassigned parties
g unassigned = 1
la var unassigned "Unassigned Parties"
* coalitions
g coalition = 0
la var coalition "Coalition of Parties"

#d;
	local partylist pp psoe iu podemos cs upyd ciu erc icv cup pnv upn bng 
	bildu pacma vox jxsi jxcat compromis fr cc cdc udc si ricat pa cha uv par ccd fac
	libertas green communists canary andalucia galicia basque regionalist 
	other extright equo nabai geroabai amaiur aralar
	batasuna ea nos psmen maspais prc primaries
	;
#d cr

foreach party in `partylist' {

	replace unassigned = 0 if `party' == 1
	replace coalition = coalition + 1 if `party' == 1

}
	* adjustments in coalition variable
	replace coalition = 0 if coalition == 1
	replace coalition = 1 if coalition > 1
	replace coalition = 1 if ciu == 1
	replace coalition = 1 if jxsi == 1
	replace coalition = 1 if nos == 1
	
* main acronym for each party
g PartyAcrNat = v1
la var PartyAcrNat "Aggregate Party Acronym"

foreach party in `partylist' {

	replace PartyAcrNat = upper("`party'") if `party' == 1

}

	* hierarchy
	replace PartyAcrNat = "PP" if pp==1 & coalition==1
	replace PartyAcrNat = "PSOE" if psoe==1 & coalition==1
	replace PartyAcrNat = "PODEMOS" if podemos==1 & coalition==1
	replace PartyAcrNat = "COMPROMIS" if compromis==1 & equo==1 & coalition==1
	replace PartyAcrNat = "PNV" if pnv==1 & ea==1 & coalition==1
	replace PartyAcrNat = "BILDU" if bildu==1 & ea==1 & coalition==1
	
* if unassigned, assign to 'other'
replace PartyAcrNat = "OTHER" if unassigned==1

drop v1 v2 Votes* rank

* store temporary dataset to merge back
compress
save "$data/temp/partyassign.dta", replace

* load data
use "$data/temp/AllElec_PartyRank.dta", clear

* merge
merge m:1 ElecType ElecYear ElecMonth PartyCodeNat using "$data/temp/partyassign.dta"
assert _merge!=2

replace PartyAcrNat = "OTHER" if _merge == 1
replace unassigned = 1 if _merge == 1
drop _merge

* sort
gsort ElecType -ElecYear -VotesNatPartyElec -VotesPartyElec

* drop unnecessary vars
drop VotesPartyElec VotesNatPartyElec

* store
#d;
	order ElecType ElecYear ElecMonth PartyCode PartyCodeProv PartyCodeReg 
	PartyCodeNat PartyAcr* PartyName rank unassigned coalition pp psoe
	;
#d cr
compress
save "$data/temp/AllElec_PartyAssign.dta", replace

* erase temp data
cap erase "$data/temp/partyassign.dta"

}
********************************************************************************
* finish cleaning data at mesa level
********************************************************************************
{
* preclean each election
foreach etype in 02 { 

	local type congreso
	local eyears 201911 201512
	
	* loop over election years
	foreach yearmo in `eyears' {
		
* load electoral data
use "$data/temp/`type'`yearmo'_f10.dta", clear

	* adjustments
	if "`yearmo'" == "201512" {
	 replace PartyCode = "0" + substr(PartyCode,2,5) if MunCode=="999" & ProvCode!="99"
	}
	
	* file to merge
	local mergefile = "$data/temp/`type'`yearmo'_f9.dta"


* add party code (national) information
merge m:1 ElecType ElecYear ElecMonth PartyCode using "$data/temp/AllElec_PartyAssign.dta", keepusing(PartyAcrNat)

* keep observations from election
drop if _merge==2
assert _merge==3
drop _merge

* destring votes variable
destring VotesParty, force replace

* generate new votes variable aggregating by PartyAcrNat
bys ElecType ElecYear ElecMonth RegCode ProvCode MunCode DistrictCode SectionCode MesaCode PartyAcrNat: egen x= sum(VotesParty)

* remove duplicates
duplicates drop ElecType ElecYear ElecMonth RegCode ProvCode MunCode DistrictCode SectionCode MesaCode PartyAcrNat, force

* drop party code
drop PartyCode

* rename votes variable
drop VotesParty 
rename x Votes

* aggregate at the mesa level
#d;
	local locid ElecType ElecYear ElecMonth RegCode ProvCode MunCode 
	DistrictCode SectionCode MesaCode
	;
#d cr
reshape wide Votes, i(`locid') j(PartyAcrNat) string

* add information at the mesa level
merge 1:1 ElecType ElecYear ElecMonth RegCode ProvCode MunCode DistrictCode SectionCode MesaCode using "`mergefile'"
drop _merge

* destring
destring Census* Votes* ElecYear ElecMonth, force replace

* adjustment to census variable - not available in Catalan elections
replace CensusCERE = CensusCERE/10000
		
* add muninicipality name
merge m:m RegCode ProvCode MunCode using "$data/temp/`type'`yearmo'_f5.dta", keepusing(MunName)
drop if _merge==2
drop _merge

* store
compress
save "$data/temp/`type'`yearmo'_wide.dta", replace

	}
} 

* put everything together
clear
foreach etype in 02 { 

	local type congreso
	local eyears 201911 201512


	* loop over election years
	foreach yearmo in `eyears' {

		* append data
		append using "$data/temp/`type'`yearmo'_wide.dta", force
		* erase temp data
		cap erase "$data/temp/`type'`yearmo'_wide.dta"
	
	} 
} 

* abstention
g Abstention = CensusINE - VotesParties - VotesBlank - VotesNull 
* valid votes
g VotesValid = VotesParties + VotesBlank
drop OfficialData VotesCERE

* muncode and province
g muncode = ProvCode + MunCode
la var muncode "Municipality Code"

* province
cap drop ProvName
destring ProvCode, g(prov)
* add labels
label def province 1 "Alaba" 2 "Albacete" 3 "Alicante" 4 "Almeria" 5 "Avila" /*
*/ 6 "Badajoz" 7 "Baleares" 8 "Barcelona" 9 "Burgos" 10 "Caceres" 11 "Cadiz" /*
*/ 12 "Castellon" 13 "Ciudad Real" 14 "Cordoba" 15 "Coruña" 16 "Cuenca" /*
*/ 17 "Girona" 18 "Granada" 19 "Guadalajara" 20 "Gipuzkoa" 21 "Huelva" /*
*/ 22 "Huesca" 23 "Jaen" 24 "Leon" 25 "Lleida" 26 "Rioja" 27 "Lugo" 28 "Madrid" /*
*/ 29 "Malaga" 30 "Murcia" 31 "Navarra" 32 "Ourense" 33 "Asturias" 34 "Palencia" /*
*/ 35 "Palmas" 36 "Pontevedra" 37 "Salamanca" 38 "Santa Cruz de Tenerife" /*
*/ 39 "Cantabria" 40 "Segovia" 41 "Sevilla" 42 "Soria" 43 "Tarragona" 44 "Teruel" /*
*/ 45 "Toledo" 46 "Valencia" 47 "Valladolid" 48 "Bizkaia" 49 "Zamora" 50 "Zaragoza" /*
*/ 51 "Ceuta" 52 "Melilla"
la val prov province
la var prov "Province"

* region codes
g NewReg = ""
replace NewReg = "11" if RegCode == "10" 
replace NewReg = "12" if RegCode == "11" 
replace NewReg = "13" if RegCode == "12" 
replace NewReg = "15" if RegCode == "13" 
replace NewReg = "16" if RegCode == "14" 
replace NewReg = "14" if RegCode == "15" 
replace NewReg = "17" if RegCode == "16" 
replace NewReg = "10" if RegCode == "17" 
replace RegCode = NewReg if NewReg != ""
drop NewReg

* region
destring RegCode, g(region)
label define region 1 "Andalucia" 2 "Aragon" 3 "Asturias" 4 "Baleares" /*
*/ 5 "Canarias" 6 "Cantabria" 7 "Castilla Leon" 8 "Castila La Mancha" /*
*/ 9 "Catalunya" 10 "Valencia" 11 "Extremadura" 12 "Galicia" 13 "Madrid" /*
*/ 14 "Murcia" 15 "Navarra" 16 "Pais Vasco" 17 "Rioja" 18 "Ceuta" 19 "Melilla" /*
*/ 0 "National"
label val region region
la var region "Region"

* etype
g etype = "congress" if ElecType == "02"
la var etype "Election Type"

* election dates
g edate=.
la var edate "Election Date"
format edate %td

replace edate = mdy(11,10,2019) if ElecYear==2019 & ElecMonth==11 & etype=="congress"
replace edate = mdy(12,20,2015) if ElecYear==2015 & etype=="congress"

* store
gsort ElecType -edate RegCode ProvCode MunCode DistrictCode SectionCode MesaCode
#d;
order ElecType etype ElecYear ElecMonth edate Reg* reg* Prov* prov* Mun* mun* District* SectionCode MesaCode
	Census* VotesValid VotesParties VotesBlank VotesNull Abstention VotesAvance*
	VotesPSOE VotesPP VotesPODEMOS VotesIU VotesCS VotesUPYD
	VotesERC VotesCDC VotesCUP
	VotesPNV VotesBILDU VotesBNG VotesVOX
	;
#d cr

* store data
compress
save "$data/temp/elections_mesa.dta", replace

}
********************************************************************************
* aggregate at the census tract level
********************************************************************************
{

* load data at the mesa level
use "$data/temp/elections_mesa.dta", clear

* variables defining aggregation level
local agglev ElecType ElecYear ElecMonth RegCode ProvCode MunCode DistrictCode SectionCode

* variables to aggregate
qui des, varlist
local vars `r(varlist)'
local omit `agglev' etype edate region prov muncode MesaCode MunName
local want : list vars - omit

* aggregate
foreach var in `want' {

	* store label of variable to aggregate
	local label : var label `var'
	* generate aggregate variable
	bys `agglev': egen t_`var' = total(`var'), missing
	* drop old variable
	drop `var'
	* rename new variable
	rename t_`var' `var'
	* assign old var label to new variable
	la var `var' "`label'"

}

* aggregate
duplicates drop `agglev', force

* drop variables defined below tract level
drop MesaCode

* generate vote percentages
qui des, varlist
local vars `r(varlist)'
local addomit VotesValid VotesParties VotesBlank VotesNull Abstention VotesAvance1 VotesAvance2 CensusINE CensusEscr CensusCERE
local agglev ElecType ElecYear ElecMonth RegCode ProvCode MunCode DistrictCode SectionCode

local omit `agglev' `addomit' etype edate region prov muncode MunName
local want : list vars - omit

foreach var in `want' {

	local label : var label `var'
	g Pct`var' = `var'/VotesValid
	la var Pct`var' "Pct `label'"

}

* final adjustments
destring MunCode, g(cmun)
destring DistrictCode, g(cdis)
destring SectionCode, g(csec) force
g x = subinstr(SectionCode, " ", "", .)
replace x = substr(x,1,3)
destring x, replace
replace csec = x if csec == . & x != .
drop x

* store
gsort ElecType -edate RegCode ProvCode MunCode DistrictCode SectionCode

#d;
order ElecType etype ElecYear ElecMonth edate Reg* reg* Prov* prov* Mun* *mun* District* cdis SectionCode csec
	Census* VotesValid VotesParties VotesBlank VotesNull Abstention VotesAvance*
	VotesPSOE Pct*PSOE *VotesPP *VotesPODEMOS *VotesIU *VotesCS *VotesUPYD
	VotesERC PctVotesERC *VotesPACMA *VotesCDC *VotesCUP
	VotesPNV PctVotesPNV *VotesBILDU *VotesBNG *VotesVOX *VotesOTHER
	;
#d cr

replace VotesOTHER=. if PctVotesOTHER>1 & PctVotesOTHER!=.
replace PctVotesOTHER=. if PctVotesOTHER>1 & PctVotesOTHER!=.

* rename
rename ProvCode CPRO
rename MunCode CMUN
rename DistrictCode CDIS
rename SectionCode CSEC

* store
compress
save "$data/int/elections_SecCen.dta", replace

}
********************************************************************************
* additional variables - barcelona elections
********************************************************************************
{
use "$data/int/elections_SecCen.dta", clear

* keep only bcn
keep if CPRO=="08" & CMUN == "019"

foreach var in ERC CUP PODEMOS JXCAT PP PSOE VOX CS PACMA UPYD {
	
	replace PctVotes`var' = 0 if PctVotes`var'==. & edate!=.

}

* separate into ideologies
g PctVotesLEFT = PctVotesERC + PctVotesCUP + PctVotesPSOE + PctVotesPODEMOS
g PctVotesRIGHT = PctVotesPP + PctVotesCS + PctVotesVOX
g PctVotesExtLEFT = PctVotesCUP + PctVotesPODEMOS
replace PctVotesLEFT = 1 - PctVotesRIGHT if PctVotesLEFT>1
la var PctVotesLEFT "Share Votes Left-wing Parties"
la var PctVotesExtLEFT "Share Votes Extreme Left-wing Parties"
la var PctVotesRIGHT "Share Votes Right-wing Parties"

* sort most recent first
order ElecType ElecYear ElecMonth CMUN CDIS CSEC

sort ElecType CMUN CDIS CSEC ElecYear ElecMonth

#d;
	local vars PctVotesLEFT PctVotesRIGHT PctVotesExtLEFT PctVotesPODEMOS 
	PctVotesPSOE PctVotesCUP PctVotesERC PctVotesPP
	PctVotesCS PctVotesVOX
	;
#d cr

foreach var in `vars' {

	bys ElecType CMUN CDIS CSEC: g `var'_lastE = `var'[_n-1]
	la var `var'_lastE "`var' in last election (of same type)"

}

* keep relevant vars
keep ElecType ElecYear ElecMonth CMUN CDIS CSEC PctVotes*LEFT PctVotesRIGHT *_lastE

* remove white space
replace CSEC = subinstr(CSEC, " ", "", .)

* store
compress
save "$data/int/elections_bcn.dta", replace

* erase temp files
cap erase "$data/temp/elections_mesa.dta"
cap erase "$data/temp/AllElec_PartyAssign.dta"
cap erase "$data/temp/AllElec_PartyRank.dta"
foreach date in 201512 201911 {
	forval i = 1(1)10 {
cap erase "$data/temp/congreso`date'_f`i'.dta"
	}
}

}
********************************************************************************
* closing
********************************************************************************
{
cap log close
clear
}
